"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : 功能测试 -- gsql执行统计时间测试
Case Name   : 创建有字符值类型列的表,查看db_tim和子时间的误差在10%以内
Create At   : 2023-12-11
Owner       : @kyeleze
Description :
    1. 创建有字符值类型列的表，执行dml
    2. 查看每个sql的db_tim和子时间的误差
Expect      :
    1. 执行成功
    2. 误差在10%以内
History     :
"""
import os
import unittest

from yat.test import Node

from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant


class DBTimeCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f"-----{os.path.basename(__file__)} start-----")
        self.user_node = Node('PrimaryDbUser')
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.table_name = 't_dbtime_test_001'
        self.user_param = f'-U {self.user_node.db_user} ' \
                          f'-W {self.user_node.db_password}'
        step = '----清理统计信息----'
        self.log.info(step)
        clean_sql = "select * from reset_unique_sql('global', 'all', 0);"
        result = self.primary_sh.execut_db_sql(clean_sql,
                                               sql_type=self.user_param)
        self.log.info(result)
        self.assertIn('t', result.strip().splitlines()[2], '执行失败' + step)

    def test_sql_dbtime(self):
        step = '----step1:创建有字符值类型列的表，执行dml expect:执行成功----'
        self.log.info(step)
        create_table = f'drop table if exists {self.table_name};' \
                       f'create table {self.table_name} ' \
                       f'(ct_col1 character(4), ct_col2 clob);'
        result = self.primary_sh.execut_db_sql(create_table,
                                               sql_type=self.user_param)
        self.log.info(result)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, result,
                      '执行失败' + step)
        dml_sql = f"insert into {self.table_name} values('test'," \
                  f"'abcdefghijklmnopqrstuvwxyz1234567890" \
                  f"~\\!@#$%^&*()_+[]\\\"?><');" \
                  f"select * from  {self.table_name};" \
                  f"update {self.table_name} set ct_col1='abc' " \
                  f"where ct_col1='test';" \
                  f"delete from {self.table_name} where ct_col1='test';" \
                  f"drop table {self.table_name};"
        result = self.primary_sh.execut_db_sql(dml_sql,
                                               sql_type=self.user_param)
        self.log.info(result)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, result,
                      '执行失败' + step)
        self.assertIn('1 row', result, '执行失败' + step)
        self.assertIn(self.constant.UPDATE_SUCCESS_MSG, result,
                      '执行失败' + step)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, result,
                      '执行失败' + step)

        step = '--step2:查看每个sql的db_tim和子时间的误差 expect:误差在10%以内--'
        self.log.info(step)
        sql_list = [f'create table {self.table_name}',
                    f'insert into {self.table_name}',
                    f'select * from {self.table_name}',
                    f'update {self.table_name}',
                    f'drop table {self.table_name}']
        for sql in sql_list:
            check_sql = f"select (select round((db_time-total_time)/db_time," \
                        f"2) from (select db_time, sum(execution_time + " \
                        f"parse_time + plan_time + rewrite_time + " \
                        f"pl_execution_time + pl_compilation_time + " \
                        f"data_io_time + net_send_time + srt1_q + " \
                        f"srt2_simple_query + srt3_analyze_rewrite + " \
                        f"srt4_plan_query + srt5_light_query + srt6_p + " \
                        f"srt7_b + srt8_e + srt9_d + srt10_s + srt11_c + " \
                        f"srt12_u + srt13_before_query + srt14_after_query) " \
                        f"as total_time from get_instr_unique_sql() where " \
                        f"query like '{sql}%' group by db_time)) < 0.1 as res;"
            result = self.primary_sh.execut_db_sql(check_sql,
                                                   sql_type=self.user_param)
            self.log.info(result)
            self.assertIn('', result.strip().splitlines()[2],
                          '执行失败' + step)

    def tearDown(self):
        self.log.info(f'-----{os.path.basename(__file__)} end-----')

